﻿CREATE TABLE [dbo].[Tool] (
    [ToolID]          INT           IDENTITY (1, 1) NOT NULL,
    [AuthorID]        INT           NULL,
    [Author]          VARCHAR (50)  NULL,
    [Title]           VARCHAR (100) NULL,
    [Description]     VARCHAR (MAX) NULL,
    [DescriptionRich] VARCHAR (MAX) NULL,
    [Compatibility]   VARCHAR (50)  NULL,
    [ProductionSafe]  VARCHAR (500) NULL,
    [ToolAdded]       DATETIME      CONSTRAINT [DF_Tool_ToolAdded] DEFAULT (getdate()) NULL,
    [AddedBy]         VARCHAR (100) CONSTRAINT [DF_Tool_AddedBy] DEFAULT (suser_sname()) NULL,
    [Rank]            INT           NULL,
    CONSTRAINT [PK1] PRIMARY KEY NONCLUSTERED ([ToolID] ASC),
    CONSTRAINT [FK_Tool_Authors] FOREIGN KEY ([AuthorID]) REFERENCES [dbo].[Authors] ([AuthorID])
);


GO
CREATE TRIGGER UpdateAuthorName
   ON  dbo.Tool
   AFTER INSERT, UPDATE
AS 
BEGIN
	
	SET NOCOUNT ON;

    MERGE Tool AS [tgt]
        USING 
            ( SELECT    author ,
                        authorid
              FROM      authors
            ) AS [src]
        ON ( tgt.authorid = src.authorid )
        WHEN MATCHED 
            THEN
	UPDATE     SET
            [tgt].author = src.author;

END